import datetime
import json

import pymysql

import C
import R

config = C.config['mysql']


def optcheck(f):
    def decorator(*args, **kw):
        if C.inpublic():
            print('操作检查: 演示环境不允许该操作 %s()' % f.__name__)
            return R.error("演示环境不允许该操作")
        else:
            return f(*args, **kw)

    return decorator


def conn():
    try:
        con = pymysql.connect(**config)
        con.autocommit(True)
        return con
    except:
        print('#' * 100)
        print('数据库连接异常: 请确认连接信息是否正确')
        print(config)
        print('#' * 100)
        print()
    return None


@optcheck
def db_delete_poster(uid, id):
    with conn() as con:
        c = con.cursor()
        c.execute("update fp_poster set is_delete=1 where id=%s and uid=%s", (id, uid))
    return R.ok()


def db_save_share(code, poster_id, param):
    with conn() as con:
        c = con.cursor()
        params = [code, poster_id, param, now_str()]
        c.execute("insert into links (code, pid, params, create_time) values (?, ?, ?, ?)", params)
        con.commit()
        return c.lastrowid


def query_user_posters(uid):
    with conn() as con:
        c = con.cursor()
        r = c.execute(
            'select id,uuid,name,preview,status,visits,create_time from fp_poster where uid=%s and is_delete=0 order by id desc',
            [uid])
        posters = []
        if r >= 1:
            rs = c.fetchall()
            for row in rs:
                posters.append({
                    'id': row[0],
                    'uuid': row[1],
                    'name': row[2],
                    'preview': row[3],
                    'status': row[4],
                    'visits': row[5],
                    'createTime': row[6]
                })
        return posters


def query_user_poster(uid=None, id=None, uuid=None):
    with conn() as con:
        c = con.cursor()

        if uuid:
            r = c.execute(
                'select id,uuid,name,preview,status,visits,create_time,json from fp_poster where uuid=%s and is_delete=0 limit 1',
                [uuid])
        else:
            r = c.execute(
                'select id,uuid,name,preview,status,visits,create_time,json from fp_poster where uid=%s and id=%s and is_delete=0 limit 1',
                [uid, id])

        if r >= 1:
            row = c.fetchone()
            return {
                'id': row[0],
                'uuid': row[1],
                'name': row[2],
                'preview': row[3],
                'status': row[4],
                'visits': row[5],
                'createTime': row[6],
                'json': row[7],
            }
    return None


def query_user_fonts(uid):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id,code,name,type,create_time from fp_fonts where uid=%s or type=1', [uid])
        if r >= 1:
            return [{
                'id': row[0],
                'code': row[1],
                'name': row[2],
                'type': row[3],
                'createTime': row[4],
                'path': C.STORE_FONTS + row[1],
            } for row in c.fetchall()]
    return []


def query_batchs(uid):
    with conn() as con:
        c = con.cursor()
        params = [uid]
        sql = 'select b.id, b.name, b.type, b.status, b.num, b.done_num, b.path from fp_batch b where (b.uid=%s) and b.is_delete=0'
        sql += ' order by b.update_time desc'
        r = c.execute(sql, params)
        if r >= 1:
            return [{
                'id': row[0],
                'name': row[1],
                'type': row[2],
                'status': row[3],
                'num': row[4],
                'doneNum': row[5],
                'path': row[6],
            } for row in c.fetchall()]
        return []


def query_batch(uid, id):
    with conn() as con:
        c = con.cursor()
        params = [uid, id]
        sql = 'select b.id, b.name, b.type, b.status, b.num, b.done_num, b.path, b.raw, b.create_time, b.poster_id from fp_batch b where (b.uid=%s) and b.id = %s and b.is_delete=0'
        sql += ' order by b.id desc'
        r = c.execute(sql, params)
        if r >= 1:
            row = c.fetchone()
            return {
                'id': row[0],
                'name': row[1],
                'type': row[2],
                'status': row[3],
                'num': row[4],
                'doneNum': row[5],
                'path': row[6],
                'raw': row[7],
                'createTime': row[8],
                'posterId': row[9],
            }


def query_materials(param):
    with conn() as con:
        c = con.cursor()
        params = [param['uid']]
        sql = "select id,filename,path,uid from fp_store where (uid=%s or uid=0) and is_delete=0 and content_type like concat('%%', 'image', '%%') "
        if param['name']:
            sql += " and filename like concat('%%', %s, '%%')"
            params.append(param['name'])
        sql += ' order by id desc'
        r = c.execute(sql, params)
        if r >= 1:
            return [{
                'id': row[0],
                'filename': row[1],
                'path': row[2],
                'uid': row[3],
            } for row in c.fetchall()]
    return []


@optcheck
def db_delete_material(uid, id):
    with conn() as con:
        c = con.cursor()
        c.execute("update fp_store set is_delete=1 where id=%s and uid=%s", (id, uid))
    return R.ok()


def find_link(code):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id from fp_link where code=%s limit 1', [code])
        return r >= 1
    return None


def find_link_param(code):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id, uid, poster_id, params from fp_link where code=%s limit 1', [code])
        if r >= 1:
            row = c.fetchone()
            link_id = row[0]
            uid = row[1]
            poster_id = row[2]
            param = json.loads(row[3])

            p = query_user_poster(uid, poster_id)
            if not p:
                return None
            d = merge_params(p, param)
            return poster_id, link_id, uid, d
    return None, None, None, None


def find_build_data(uuid, param):
    p = query_user_poster(uuid=uuid)
    if p is None:
        return None
    return merge_params(p, param)


def merge_params(p, param):
    d = json.loads(p['json'])
    for item in d['items']:
        vd = item['vd'].strip()
        if vd:
            if not param.get(vd, None):
                continue
            v = param[vd]
            if v:
                item['v'] = v.strip()
    # 处理背景图片
    if param.get('bgUrl', None):
        d['bgUrl'] = param['bgUrl']
    return d


def save_link(params):
    with conn() as con:
        c = con.cursor()
        c.execute('insert fp_link (uid, poster_id, code, params, create_time) values (%s,%s,%s,%s, now())', params)
        return c.lastrowid
    return None


def find_store(uid, checksum):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id,filename,path from fp_store where uid=%s and checksum=%s and is_delete=0 limit 1',
                      [uid, checksum])
        if r >= 1:
            row = c.fetchone()
            return {
                'id': row[0],
                'filename': row[1],
                'path': row[2],
            }
    return None


def save_store(d):
    with conn() as con:
        c = con.cursor()
        c.execute(
            'insert fp_store (uid, filename, path, size, checksum, content_type, create_time) values (%s,%s,%s,%s,%s,%s, now())',
            d)
        return c.lastrowid
    return None


def now_str(days=0):
    return (datetime.datetime.now() + datetime.timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')


def save_user_poster(uid, d):
    name = d['name']
    uuid = d['uuid']
    preview = d['preview']
    del d['uuid']
    del d['preview']
    if d.get('_t'):
        del d['_t']
    with conn() as con:
        c = con.cursor()
        params = [uid, uuid, name, preview, now_str(), json.dumps(d)]
        r = c.execute("insert into fp_poster (uid,uuid,name,preview,create_time,json) values (%s, %s, %s, %s, %s, %s)",
                      params)
        return c.lastrowid


def update_user_poster(uid, d):
    name = d['name']
    with conn() as con:
        c = con.cursor()
        params = [name, d['preview'], json.dumps(d), d['id'], uid]
        r = c.execute("update fp_poster set name=%s, preview=%s, json=%s where id=%s and uid=%s", params)
        return c.lastrowid


def copy_user_poster(uid, poster):
    d = json.loads(poster['json'])
    if d.get('id'):
        del d['id']  # 删除
    d['uuid'] = poster['uuid']
    d['preview'] = poster['preview']
    d['name'] = poster['name'] + '-副本'
    save_user_poster(uid, d)


def query_token(token):
    with conn() as con:
        c = con.cursor()
        r = c.execute(
            'select uid from fp_token where code = %s and (expire_time >= %s or expire_type = 2) and status=1 limit 1',
            [token, now_str()])
        if r >= 1:
            return c.fetchone()
    return None


def query_userinfo(id):
    with conn() as con:
        c = con.cursor()
        r = c.execute(
            'select id, username, status, password, type,member_id,jk_username,vip_id,vip_name,end_time,auth,vip_days from fp_user where id=%s limit 1',
            [id])
        if r >= 1:
            r = c.fetchone()
            uid = r[0]
            return {
                'id': uid,
                'username': r[1],
                'status': r[2],
                'password': r[3],
                'type': r[4],

                'member_id': r[5],
                'jk_username': r[6],
                'vip_id': r[7],
                'vip_name': r[8],
                'end_time': r[9],
                'auth': r[10],
                'vip_days': r[11],
            }
    return None


def total(sql, params):
    sql = f'select count(1) from ( {sql} ) t '
    with conn() as con:
        c = con.cursor()
        c.execute(sql, params)
        return c.fetchone()[0]
    return 0


def admin_query_users(d):
    with conn() as con:
        c = con.cursor()
        sql = "select id, username, status, password, create_time, remark from fp_user where type = 1 and is_delete = 0"
        kw = d['keyword']
        status = d['status']
        params = []
        if kw:
            sql += " and username like concat('%%', %s, '%%')"
            params.append(kw)
        if status:
            sql += " and status = %s"
            params.append(status)
        sql += ' order by id desc'
        num = total(sql, params)
        sql += ' limit %s, %s'
        params.append(d['_start'])
        params.append(d['_limit'])
        list = []
        if c.execute(sql, params) >= 1:
            rs = c.fetchall()
            list = [{
                'id': r[0],
                'username': r[1],
                'status': r[2],
                'password': r[3],
                'createTime': r[4],
                'remark': r[5],
            } for r in rs]
        return R.ok().add('total', num).add("list", list)
    return None


def admin_query_user(uid):
    with conn() as con:
        c = con.cursor()
        sql = "select id, username, status, password, create_time, remark from fp_user where type = 1 and is_delete = 0 and id=%s"
        if c.execute(sql, [uid]) >= 1:
            r = c.fetchone()
            user = {
                'id': r[0],
                'username': r[1],
                'status': r[2],
                'password': r[3],
                'createTime': r[4],
                'remark': r[5],
            }
        return R.ok().add("user", user)
    return None


def admin_update_user(d):
    with conn() as con:
        c = con.cursor()
        c.execute('update fp_user set username=%s, status=%s, password=%s, remark=%s where id=%s',
                  [d['username'], d['status'], d['password'], d['remark'], d['id']])
    return R.ok()


def admin_add_user(d):
    with conn() as con:
        c = con.cursor()
        try:
            r = c.execute(
                'insert into fp_user (username, status, password , remark, create_time) values (%s,%s,%s,%s,now())',
                [d['username'], d['status'], d['password'], d['remark']])
            # 生成用户后，默认生成一个用户API凭证
            uid = c.lastrowid
            token = C.code(32)
            c.execute(
                'insert into fp_token (uid, code, create_time, expire_time, type) values (%s, %s, now(), date_add(now(), INTERVAL 365 DAY), 2)',
                (uid, token))
        except (pymysql.err.IntegrityError):
            return R.error('该记录已经存在')
    return R.ok()


def admin_delete_user(uid):
    with conn() as con:
        c = con.cursor()
        c.execute('update fp_user set is_delete=1 where id=%s', [uid])
    return R.ok()


def query_user_tokens(uid):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id, code, status, remark, create_time from fp_token where uid=%s and type=2 limit 10',
                      [uid])
        if r >= 1:
            rs = c.fetchall()
            rows = []
            for r in rs:
                rows.append({
                    'id': r[0],
                    'code': r[1],
                    'status': r[2],
                    'remark': r[3],
                    'createTime': r[4],
                })
            return rows
    return None


def update_user_token(d):
    with conn() as con:
        c = con.cursor()
        c.execute('update fp_token set remark=%s, status=%s where id=%s and uid=%s',
                  [d['remark'], d['status'], d['id'], d['uid']])
    return R.ok()


def login(d):
    print(d)
    with conn() as con:
        c = con.cursor()
        r = c.execute(
            'select id, username, status, type from fp_user where username = %s and password = %s and status=1 limit 1',
            [d['username'], d['password']])
        if r >= 1:
            r = c.fetchone()
            uid = r[0]
            user = query_userinfo(uid)
            r = c.execute('select id from fp_token where uid = %s and type = 1 limit 1', [uid])
            token = C.code(32)
            if r >= 1:
                tid = c.fetchone()[0]
                c.execute('update fp_token set code=%s, expire_time = date_add(now(), INTERVAL 10 DAY) where id = %s',
                          [token, tid])
            else:
                c.execute(
                    'insert into fp_token (uid, code, create_time, expire_time) values (%s, %s, now(), date_add(now(), INTERVAL 10 DAY))',
                    (uid, token))
            return R.ok().add('user', user).add('token', token)
    return R.error('登录失败')


def logout(token):
    with conn() as con:
        c = con.cursor()
        c.execute('update fp_token set expire_time=now() where code=%s', [token])
    return R.ok()


@optcheck
def changepwd(uid, pwd):
    with conn() as con:
        c = con.cursor()
        c.execute('update fp_user set password=%s where id=%s', [pwd, uid])
    return R.ok()


def save_batch(d, uid):
    d['raw'] = d['raw'].replace('\r\n', '\n')
    with conn() as con:
        c = con.cursor()
        params = [uid, d['name'], d['type'], d['posterId'], d['raw'], d['num'], d['status'], now_str()]
        r = c.execute(
            "insert into fp_batch (uid, name, type, poster_id, `raw`, num, `status`, create_time) values (%s, %s, %s, %s, %s, %s, %s, %s)",
            params)
        return c.lastrowid


def update_batch(d, uid):
    d['raw'] = d['raw'].replace('\r\n', '\n')
    with conn() as con:
        c = con.cursor()
        params = [d['name'], d['type'], d['posterId'], d['raw'], d['num'], d['status'], uid, d['id']]
        r = c.execute(
            "update fp_batch set name=%s, type=%s, poster_id=%s, `raw`=%s, num=%s, status=%s, done_num=0 where uid=%s and id=%s",
            params)
        return r


def update_batch_process(done_num, batchId):
    with conn() as con:
        c = con.cursor()
        params = [done_num, batchId]
        r = c.execute(
            "update fp_batch set done_num=%s where id=%s",
            params)
        return r


def delete_batch(uid, batchId):
    with conn() as con:
        c = con.cursor()
        params = [batchId, uid]
        r = c.execute(
            "update fp_batch set is_delete=1 where id=%s and uid=%s",
            params)
    return R.ok()


def update_batch_process_done(status, msg, path, batchId):
    with conn() as con:
        c = con.cursor()
        params = [status, msg, path, batchId]
        r = c.execute(
            "update fp_batch set status=%s, error_msg=%s, path=%s where id=%s",
            params)
        return r


def find_user(username):
    with conn() as con:
        c = con.cursor()
        r = c.execute('select id, username, status, type from fp_user where username=%s limit 1', [username])
        if r >= 1:
            r = c.fetchone()
            uid = r[0]
            return {
                'id': uid,
                'username': r[1],
                'status': r[2],
                'type': r[3],
            }
    return None


def add_user(username, member_id, jk_username, vip_id, vip_name, end_time, auth,vip_days):
    with conn() as con:
        c = con.cursor()
        r = c.execute(
            "insert into fp_user (username, status, create_time, remark,password,member_id, jk_username, vip_id, vip_name, end_time, auth,vip_days) values (%s,1,now(), 'AUTO','123456',%s,%s,%s,%s,%s,%s,%s)",
            [username, member_id, jk_username, vip_id, vip_name, end_time, auth,vip_days])
        # 生成用户后，默认生成一个用户API凭证
        uid = c.lastrowid
        return {
            'id': uid
        }
    return None


def save_token(uid, token):
    with conn() as con:
        c = con.cursor()
        c.execute(
            "insert into fp_token (uid, code, create_time, expire_time, type, expire_type, status, remark) values (%s, %s, now(), date_add(now(), INTERVAL 365 DAY), 2, 2, 1, 'AUTO')",
            (uid, token))
        return c.lastrowid
